
USE [ppjdb]
GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwLookUp]'))
DROP VIEW [dbo].[vwLookUp]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[vwLookUp] 
AS 
with t0 as (
select LookUpSubType as LookUpID,LookUpShortName as LookUpName
	from tblLookUp (nolock) 
	where LookUpSubID = 0
)
select t0.*,
t.LookUpShortName,t.LookUpSubType,t.LookUpDescription,t.col1,t.col2,t.col3
	from tblLookUp t (nolock) 
	inner join t0 on t0.LookUpID = t.LookUpSubID
	where t.LookUpSubID <> 0

GO


